﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Specialized;
using WP.DB;
using WP.RM.DataEntity;
using WP.Commons;

namespace WP.RM.DataAccess
{
    public class RMProductBOMSalesDAL : IDataAccess
    {
        public RMProductBOMSalesDAL()
		{
		}
        #region Select List
        public string SQLList(int iParameter)
        {
            //string strSQL = @"select RecID,CategoryName,SetName ,ProductName,VersionNo,CreateDate,Creator,TotalCost,TotalCostRev,TotalCostDiff,StandardUnitCost,VerUnitCost,StandardUnitCost-VerUnitCost as [差异] from rmProductBOMSales order by ProductName, VersionNo";
            string strSQL = @"select RecID,CategoryName,SetName,ProductName,SalesProductName,TechnicsName,VersionNo,CreateDate,Creator,StandardUnitCost,VerUnitCost,VerUnitCost-StandardUnitCost as [差异] from rmProductBOMSales where VerClosed=0 order by ProductName, VersionNo";
            return strSQL;
        }
        #endregion

        #region Fields
        public string Fields
        {
            get
            {
                return @" CategoryID,SetID,ProductFItemID,CategoryName,SetName,ProductName, VersionNo,VersionRemark,CreateDate,Creator,TechnicsName,
                          TotalLabourCost,TotalWastageCost,TotalDetailCost,TotalWorkCost,TotalCost,Remark, 
                          TotalLabourCostRev,TotalWastageCostRev,TotalDetailCostRev,TotalWorkCostRev,TotalCostRev,RemarkRev,
                          TotalLabourCostDiff,TotalWastageCostDiff,TotalDetailCostDiff,TotalWorkCostDiff,TotalCostDiff,RemarkDiff,
                          VerClosed,StartCost,VersionCost,OtherCost ,AvgUnitCost  ";
            }
        }
        #endregion

        public string ParameterFields
        {
            get
            {
                return @"LabelUnitQty,	LabelLength,	LabelWidth ,LabelSize,	PrintLength,
	                        PrintWidth ,	LaminatingLength ,	LaminatingWidth ,	CutFactor ,
	                        PrintUseQty, 	ContainQty ,	PackageQty ,	InnerDiameterLength,
	                        InnerDiameterWidth ,	InnerDiameterHeight,	InnerOutsideDiff ,	MiddleWidth,	
                            BurrWidth ,	OtherPrintWidth ,	OtherPrintBackWidth ,	UseIronNailQty ,ProdDiameter,
                            LockingWireQty ,LockingWireLength,Quantity,ProductLength,ProductWidth,Pages,PaperHeight,SetSize,PagePrintWidth,PagePrintLength ";               
            }

        }
        string strLabourCost = @"RowSeqNo,BOMSeqNo ,SeqNo,WKID,WKName,WKRemark,CategoryID,CategoryName,SetID,SetName,VersionNo,ProductFItemID,ProductName,FItemID,ItemNo,
                                 CalUnit ,	CutType ,  WorkQty ,  TotalQty,	StandardQty ,	SpendHours,	StandardWage ,	StandardWage26 ,	UnitCost ,	Remark  ,
                                 DetailCost,WorkCost, DetailTotWorkCost,IsKeyWK , TechnicsName
                                ";

        string strDetails = @"RowSeqNo ,SeqNo,VersionNo,ProductFItemID,ProductName,WKID,WKName,WKRemark,CategoryID,CategoryName,SetID,SetName,TechnicsName,
                              IsKeyWK,IsInputWK,FItemID,  ItemNo, ItemName,SupNo,SupName, Price, PriceUnit,
                              Qty, WastageRate,DetailCost, RatedCost,LabourCost, TotalCost,WastageCost";

        public bool UpdateHdrbyBOMDetail(DBAccess dbcon, string strCategoryID, string strSetID,string strProductFItemID, string strTechnicsName, string strVersionNo)
        {
            double dblTotalDetailCost = 0;
            double dblTotalLabourCost = 0;
            double dblTotalWastageCost = 0;
            double dblTotalWorkCost = 0;
            string strFieldName = "CategoryID,CategoryName,SetID ,SetName, ProductFItemID ,ProductName,VersionNo,TechnicsName";

            string strSQL = "select * from rmProductBOMSales where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{3}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID,strProductFItemID,strVersionNo);
            DataRow drHdr = dbcon.GetDataFirstRow(strSQL);
            if (drHdr == null)
            {
                return true;
                drHdr = dbcon.GetDataTable(strSQL).NewRow();
                drHdr["CategoryID"] = strCategoryID;
                drHdr["SetID"] = strSetID;
                drHdr["ProductFItemID"] = strProductFItemID;
                drHdr["VersionNo"] = strVersionNo;

                drHdr["CategoryName"] =DataFunctions.GetFieldValue (dbcon, "rmCategory","CategoryName" ,strCategoryID);
                drHdr["SetName"] =DataFunctions.GetFieldValue (dbcon,"rmSet","SetName" ,strSetID);
                drHdr["ProductName"] =DataFunctions.GetFieldValue (dbcon, "rmProduct", "ProductName",strProductFItemID);

                DataFunctions.UpdateTable(dbcon, strFieldName, "rmProductBOMSales", drHdr, FormStatus.AddNew);
            }
            else
            {
                DataFunctions.UpdateTable(dbcon, strFieldName, "rmProductBOMSales", drHdr, FormStatus.Update);
            }

            strSQL = "select sum(DetailCost),sum(LabourCost),sum(WastageCost) from rmProductBOMDetailSales where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{3}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID,strVersionNo);
            DataRow drTotal = dbcon.GetDataFirstRow(strSQL);
            if (drTotal != null)
            {
                dblTotalDetailCost = CvtFunc.ToDouble(drTotal[0]);               
                dblTotalWastageCost = CvtFunc.ToDouble(drTotal[2]);
            }

            strSQL = "select sum(DetailCost),sum(DetailTotWorkCost) from rmProductBOMLabourCostSales where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{3}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID,strVersionNo );
            drTotal = dbcon.GetDataFirstRow(strSQL);
            if (drTotal != null)
            {
                dblTotalLabourCost = CvtFunc.ToDouble(drTotal[0]);
                dblTotalWorkCost = CvtFunc.ToDouble(drTotal[1]);
            }

            double dblTotalCost = dblTotalDetailCost + dblTotalLabourCost + dblTotalWastageCost + dblTotalWorkCost ;
            double dblVerUnitCost=0;
            if (CvtFunc.ToDouble(drHdr["Quantity"]) > 0)
            {
                dblVerUnitCost = dblTotalCost / CvtFunc.ToDouble(drHdr["Quantity"]);
            }

            strSQL = @" update rmProductBOMSales set TotalDetailCostRev='{3}',TotalLabourCostRev='{4}',TotalWastageCostRev='{5}' ,TotalCostRev='{6}',TotalWorkCostRev='{7}'
                        where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{8}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, dblTotalDetailCost, dblTotalLabourCost,
                                            dblTotalWastageCost.ToString ("f6"), dblTotalCost,dblTotalWorkCost,strVersionNo);
            dbcon.ExecuteSql(strSQL);

            if (strTechnicsName == "")
            {
                strTechnicsName = DataFunctions.GetFieldValue(dbcon, "rmProductBOMRev", "TechnicsName", "CategoryName='" + strCategoryID + "' and SetName='" + strSetID + "' and ProductFItemID='" + strProductFItemID + "' and VersionNo='" + strVersionNo + "'");
            }
            double dblTotalDetailCostSTD = 0, dblTotalLabourCostSTD = 0, dblTotalWastageCostSTD = 0, dblTotalCostSTD = 0, dblTotalWorkCostSTD = 0, dblStandardUnitCost=0;
            strSQL = "select * from rmProductBOMHdr where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and TechnicsName='{3}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, strTechnicsName);
            DataRow drRow = dbcon.GetDataFirstRow(strSQL);
            if (drRow != null)
            {
                dblTotalDetailCostSTD = CvtFunc.ToDouble ( drRow["TotalDetailCost"]);
                dblTotalLabourCostSTD = CvtFunc.ToDouble (drRow["TotalLabourCost"]) ;
                dblTotalWastageCostSTD = CvtFunc.ToDouble(drRow["TotalWastageCost"]);
                dblTotalCostSTD = CvtFunc.ToDouble(drRow["TotalCost"]);
                dblTotalWorkCostSTD = CvtFunc.ToDouble(drRow["TotalWorkCost"]);
                dblStandardUnitCost = CvtFunc.ToDouble(drRow["StandardUnitCost"]);
            }

            strSQL = @" update rmProductBOMSales set TotalDetailCostDiff='{3}',TotalLabourCostDiff='{4}',TotalWastageCostDiff='{5}' ,TotalCostDiff='{6}',TotalWorkCostDiff='{7}',TotalCost='{9}',
                        VerUnitCost='{10}',StandardUnitCost='{11}'     
                        where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{8}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, (dblTotalDetailCost - dblTotalDetailCostSTD).ToString ("f2"), 
                        ( dblTotalLabourCost - dblTotalLabourCostSTD).ToString ("f2"),
                                            (dblTotalWastageCost - dblTotalWastageCostSTD).ToString("f2"),
                                            (dblTotalCost - dblTotalCostSTD).ToString("f2"),
                                            (dblTotalWorkCost - dblTotalWorkCostSTD).ToString("f2"), 
                                            strVersionNo,
                                            dblTotalCostSTD.ToString ("F8"),
                                            dblVerUnitCost.ToString("F4"),
                                            dblStandardUnitCost.ToString ("F4") );
            dbcon.ExecuteSql(strSQL);
            


            
            return true;
        }

        public bool UpdateBOMDetail(DBAccess dbcon, DataRow drBOM)
        {
            try
            {                
               
                if (!UpdateCost(dbcon, drBOM)) return false;
                if (CvtFunc.ToDouble(drBOM["RecID"]) > 0)
                {
                    DataFunctions.UpdateTable(dbcon, strDetails, "rmProductBOMDetailSales", drBOM, FormStatus.Update);
                }
                else
                {
                    DataFunctions.UpdateTable(dbcon, strDetails, "rmProductBOMDetailSales", drBOM, FormStatus.AddNew);
                }
                UpdateHdrbyBOMDetail(dbcon, CvtFunc.ToString(drBOM["CategoryID"]), CvtFunc.ToString(drBOM["SetID"]), CvtFunc.ToString(drBOM["ProductFItemID"]), CvtFunc.ToString(drBOM["TechnicsName"]), CvtFunc.ToString(drBOM["VersionNo"]));
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }
            return true;
        }

        public bool UpdateCost(DBAccess dbcon, DataRow drBOM)
        {
            CalQty(dbcon, drBOM);
            CalQtyWastage(dbcon, drBOM);
            CalWastageCost(dbcon, drBOM);
            CalDetailCost(dbcon, drBOM);
            CalTotalCost(dbcon, drBOM);
            //UpdateHdrbyBOMDetail(dbcon, CvtFunc.ToString(drBOM["CategoryName"]), CvtFunc.ToString(drBOM["SetName"]), CvtFunc.ToString(drBOM["ProductName"]), CvtFunc.ToString(drBOM["VersionNo"]));
            return true;
        }

        public bool CalQty(DBAccess dbcon, DataRow drBOM)   //用于计算定额用量
        {
            string strWKFormula = "";
            string strSQL="select * from rmWKProductFormulaSales where BOMRecID='"+ CvtFunc.ToString ( drBOM["RecID"] ) +"'";
            DataRow drRow = dbcon.GetDataFirstRow(strSQL);
            if (drRow != null)
            {
                strWKFormula = CvtFunc.ToString(drRow["WKFormula"]);
                strSQL = "select * from rmWKCalDict";
                DataTable dtDict = dbcon.GetDataTable(strSQL);
                foreach (DataRow drDict in dtDict.Rows)
                {
                    strWKFormula = strWKFormula.Replace(CvtFunc.ToString(drDict["CHNFieldName"]), CvtFunc.ToString(drDict["SYSTableName"]) + "." + CvtFunc.ToString(drDict["SYSFieldName"])); 
                }
                if (strWKFormula.IndexOf("ImCategoryWKRating", 0, StringComparison.OrdinalIgnoreCase) >= 0)
                {
                    double dblQtyRate = 0;
                    strSQL = "select * from ImCategoryWKRating where FItemID='{0}' and CategoryID='{1}' and WKProductID='{2}' and SupName='{3}'";
                    strSQL = string.Format(strSQL, drBOM["FItemID"], drBOM["CategoryID"], drBOM["WKID"], drBOM["SupName"]);
                    DataRow drRateQty = dbcon.GetDataFirstRow(strSQL);
                    if (drRateQty != null)
                    {
                        dblQtyRate = CvtFunc.ToDouble(drRateQty["RateQty"]);
                    }
                    strWKFormula = strWKFormula.Replace("ImCategoryWKRating.RatingQty", dblQtyRate.ToString());
                }
                strWKFormula = strWKFormula.Replace("rmProduct", "rmProductBOMSales");
                if (strWKFormula.IndexOf("iminvmaster", 0, StringComparison.OrdinalIgnoreCase) < 0)
                {
                    strSQL = @" select {0} from rmProductBOMSales where  ProductFItemID='{2}' and CategoryID='{3}' and SetID='{4}' and versionno='{5}'";
                    strSQL = string.Format(strSQL, strWKFormula, drBOM["ItemNo"], drBOM["ProductFItemID"], drBOM["CategoryID"], drBOM["SetID"],drBOM["VersionNo"]);
                    DataRow drQty = dbcon.GetDataFirstRow(strSQL);
                    drBOM["Qty"] = CvtFunc.ToDouble(drQty[0]);
                }
                else
                {
                    strSQL = @" select {0}   from rmProductBOMDetailSales
                          ,iminvmaster where FItemID='{1}' and productFitemID='{2}' and CategoryID='{3}' and SetID='{4}' and SupNo='{5}' and versionno='{6}'";
                    strSQL = string.Format(strSQL, strWKFormula, drBOM["FItemID"], drBOM["ProductFItemID"], drBOM["CategoryID"], drBOM["SetID"], drBOM["SupNo"],drBOM["Versionno"] );
                    DataRow drQty = dbcon.GetDataFirstRow(strSQL);
                    drBOM["Qty"] = CvtFunc.ToDouble(drQty[0]);
                }
            } 
            return true;
        }

        public bool CalLabourCostQty(DBAccess dbcon, DataRow drBOM)   //用于计算定额用量
        {
            string strWKFormula = "";
            string strSQL = "select * from rmWKFormulaOtherProductBOMSales where BOMRecID='" + CvtFunc.ToString(drBOM["RecID"]) + "'";
            DataRow drRow = dbcon.GetDataFirstRow(strSQL);
            if (drRow != null)
            {
                strWKFormula = CvtFunc.ToString(drRow["WKFormula"]);
                strSQL = "select * from rmWKCalDict";
                DataTable dtDict = dbcon.GetDataTable(strSQL);
                foreach (DataRow drDict in dtDict.Rows)
                {
                    strWKFormula = strWKFormula.Replace(CvtFunc.ToString(drDict["CHNFieldName"]), CvtFunc.ToString(drDict["SYSTableName"]) + "." + CvtFunc.ToString(drDict["SYSFieldName"]));
                }
                if (strWKFormula.IndexOf("ImCategoryWKRating", 0, StringComparison.OrdinalIgnoreCase) >= 0)
                {
                    double dblQtyRate = 0;
                    strSQL = "select * from ImCategoryWKRating where FItemID='{0}' and CategoryID='{1}' and WKProductID='{2}' and SupName='{3}'";
                    strSQL = string.Format(strSQL, drBOM["FItemID"], drBOM["CategoryID"], drBOM["WKID"], drBOM["SupName"]);
                    DataRow drRateQty = dbcon.GetDataFirstRow(strSQL);
                    if (drRateQty != null)
                    {
                        dblQtyRate = CvtFunc.ToDouble(drRateQty["RateQty"]);
                    }
                    strWKFormula = strWKFormula.Replace("ImCategoryWKRating.RatingQty", dblQtyRate.ToString());
                }
                strWKFormula = strWKFormula.Replace("rmProduct", "rmProductBOMSales");
                if (strWKFormula.IndexOf("iminvmaster",0, StringComparison.OrdinalIgnoreCase)<0)
                {
                    strSQL = @" select {0}   from rmProductBOMSales
                          where ProductFItemID='{2}' and CategoryID='{3}' and SetID='{4}' and versionno='{5}'";
                    strSQL = string.Format(strSQL, strWKFormula, drBOM["ItemNo"], drBOM["ProductFItemID"], drBOM["CategoryID"], drBOM["SetID"],drBOM["versionno"]);
                }
                else
                {
                    strSQL = @" select {0}   from rmProductBOMSales
                          ,iminvmaster where FItemID='{1}' and ProductFItemID='{2}' and CategoryID='{3}' and SetID='{4}' and versionno='{5}' ";
                    strSQL = string.Format(strSQL, strWKFormula, drBOM["FItemID"], drBOM["ProductFItemID"], drBOM["CategoryID"], drBOM["SetID"], drBOM["versionno"]);
                }
                DataRow drQty = dbcon.GetDataFirstRow(strSQL);
                drBOM["WorkQty"] = CvtFunc.ToDouble(drQty[0]);

                double dblCutType = 0;
                string strCutType = CvtFunc.ToString(drBOM["CutType"]);
                dblCutType = CvtFunc.ToDouble(DataFunctions.GetFieldValue("rmCutType", "CutTimes", "CutType='" + strCutType + "'"));
                if (dblCutType == 0)
                {
                    dblCutType = CvtFunc.ToDouble(strCutType);
                }
                if (dblCutType != 0)
                {
                    drBOM["TotalQty"] = CvtFunc.ToDouble(drBOM["WorkQty"]) / dblCutType;
                }
                else
                {
                    drBOM["TotalQty"] = CvtFunc.ToDouble(drBOM["WorkQty"]);
                }
            }
            return true;
        }

        public bool CalQtyWastage(DBAccess dbcon, DataRow drBOM)  //用于计算损耗数量
        {
            drBOM["QtyWastage"] = CvtFunc.ToDouble(drBOM["WastageRate"]) * CvtFunc.ToDouble(drBOM["Qty"]) * 0.01;
            return true;
        }

        public bool CalWastageCost(DBAccess dbcon, DataRow drBOM)  //用于计算损耗成本
        {
            drBOM["WastageCost"] = CvtFunc.ToDouble(drBOM["QtyWastage"]) * CvtFunc.ToDouble(drBOM["Price"]);
            return true;
        }

        public bool CalDetailCost(DBAccess dbcon, DataRow drBOM)  //用于计算定额成本
        {
            drBOM["DetailCost"] = CvtFunc.ToDouble(drBOM["Qty"]) * CvtFunc.ToDouble(drBOM["Price"]);
            return true;
        }

        public bool CalTotalCost(DBAccess dbcon, DataRow drBOM)  //用于计算总成本
        {
            drBOM["TotalCost"] = CvtFunc.ToDouble(drBOM["DetailCost"]) + CvtFunc.ToDouble(drBOM["LabourCost"]) + CvtFunc.ToDouble(drBOM["WastageCost"]);                              
            return true;
        }


        public bool DeleteDetailBOM( DBAccess dbcon, string strRecID)
        {
            string strSQL = "delete from rmProductBOMDetailSales where recID='" + strRecID + "'";
            dbcon.ExecuteSql(strSQL);

            strSQL = "delete from rmWKProductFormulaSales where BOMRecID='" + strRecID + "'";
            dbcon.ExecuteSql(strSQL);
            return true;
        }

        public bool DeleteDetail_LabourCost(DBAccess dbcon, string strRecID)
        {
            string strSQL = "delete from rmProductBOMLabourCostSales where recID='" + strRecID + "'";
            dbcon.ExecuteSql(strSQL);

            strSQL = "delete from rmWKFormulaOtherProductBOMSales where BOMRecID='" + strRecID + "'";
            dbcon.ExecuteSql ( strSQL );
            return true;
        }

        #region OtherCost Grid

        public bool UpdateLabourCostDetail(DBAccess dbcon, DataRow drLabourCost)
        {
            try
            {
                CalLabourCostQty(dbcon, drLabourCost);
                CalUnitCost(dbcon, drLabourCost);
                CalSpendHours(dbcon, drLabourCost); 
                CalLabourCostDetailCost(dbcon, drLabourCost);                
                CalWorkCostDetailCost(dbcon, drLabourCost);
                if (CvtFunc.ToDouble(drLabourCost["RecID"]) > 0)
                {
                    DataFunctions.UpdateTable(dbcon, strLabourCost, "rmProductBOMLabourCostSales", drLabourCost, FormStatus.Update);
                }
                else
                {
                    DataFunctions.UpdateTable(dbcon, strLabourCost, "rmProductBOMLabourCostSales", drLabourCost, FormStatus.AddNew);
                }
                UpdateHdrbyBOMDetail(dbcon, CvtFunc.ToString(drLabourCost["CategoryID"]), CvtFunc.ToString(drLabourCost["SetID"]), CvtFunc.ToString(drLabourCost["ProductFItemID"]), CvtFunc.ToString(drLabourCost["TechnicsName"]), CvtFunc.ToString(drLabourCost["VersionNo"]));
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }
            return true;
        }

        public void CalUnitCost(DBAccess dbcon, DataRow drLabourCost)
        {
            double dblStandardQty = CvtFunc.ToDouble(drLabourCost["StandardQty"]);      //额定产量
            double dblStandardWage = CvtFunc.ToDouble(drLabourCost["StandardWage"]);    //固定工资  
            double dblStandardWage26 = CvtFunc.ToDouble(drLabourCost["StandardWage26"]);    //26天标准工资 
            if (dblStandardQty != 0)
            {
                double dblUnitCost = (dblStandardWage26 - dblStandardWage / 22d * 26) / dblStandardQty / 26d;
                drLabourCost["UnitCost"] = dblUnitCost.ToString("f6");
            }
            else
            {
                drLabourCost["UnitCost"] = 0;
            }
        }
        public void CalSpendHours(DBAccess dbcon, DataRow drLabourCost)
        {
            double dblTotalQty = CvtFunc.ToDouble ( drLabourCost["TotalQty"]);
            double dblStandardQty = CvtFunc.ToDouble(drLabourCost["StandardQty"]);
            if (dblStandardQty != 0)
            {
                drLabourCost["SpendHours"] = dblTotalQty / dblStandardQty;
            }
            else
                drLabourCost["SpendHours"] = "0";
        }

        public void CalLabourCostDetailCost(DBAccess dbcon, DataRow drLabourCost) //人工成本 = 计件工价*计量数量 + 固定工资/22/10*耗用工时
        {
            //double dblUnitCost = CvtFunc.ToDouble(drLabourCost["UnitCost"]);
            //double dblDetailCost = 0;
            //double dblTotalQty = CvtFunc.ToDouble ( drLabourCost ["TotalQty"]);
            //dblDetailCost = dblUnitCost * dblTotalQty;
            //drLabourCost["DetailCost"] = dblDetailCost;


            double dblUnitCost = CvtFunc.ToDouble(drLabourCost["UnitCost"]);
            double dblStandardWage = CvtFunc.ToDouble(drLabourCost["StandardWage"]);    //固定工资  
            double dblDetailCost = 0;
            double dblTotalQty = CvtFunc.ToDouble(drLabourCost["TotalQty"]);
            dblDetailCost = dblUnitCost * dblTotalQty + dblStandardWage / 22d / 10 * CvtFunc.ToDouble(drLabourCost["SpendHours"]);
            drLabourCost["DetailCost"] = dblDetailCost;
        }

        public void CalWorkCostDetailCost(DBAccess dbcon, DataRow drLabourCost)   //用于计算项目分摊
        {
            double dblTotWorkCost = CvtFunc.ToDouble(drLabourCost["SpendHours"]) * CvtFunc.ToDouble(drLabourCost["WorkCost"]);
            drLabourCost["DetailTotWorkCost"] = dblTotWorkCost;
        }
        #endregion


        public bool CopytoNewVer(DBAccess dbcon, DataRow drOldHdr)
        {            
            try
            {
                // init HDR
                if (drOldHdr == null) return false;
                string strCategoryName = "", strSetName = "", strProductName = "", strOldVersion = "";
                string strCategoryID = "", strSetID = "", strProductFItemID = "";
                strCategoryID = CvtFunc.ToString(drOldHdr["CategoryID"]);
                strSetID = CvtFunc.ToString(drOldHdr["SetID"]);
                strProductFItemID = CvtFunc.ToString(drOldHdr["ProductFItemID"]);

                strCategoryName = DataFunctions.GetFieldValue(dbcon, "rmCategory", "CategoryName", " CategoryID='" + drOldHdr["CategoryID"] + "'");
                strSetName = DataFunctions.GetFieldValue(dbcon, "rmSet", "SetName", " SetID='" + drOldHdr["SetID"] + "'");
                strProductName = DataFunctions.GetFieldValue(dbcon, "rmProduct", "ProductName", " ProductFItemID='" + drOldHdr["ProductFItemID"] + "'");
                strOldVersion = CvtFunc.ToString(drOldHdr["VersionNo"]);

                string strNewVisionNo = CvtFunc.ToString(CvtFunc.ToDouble(DataFunctions.GetFieldValue(dbcon, "rmProductBOMSales", "max(VersionNo)", "ProductFItemID='"+ strProductFItemID +"'")) + 1);

                DataRow drHdr = dbcon.GetDataTable("select top 0 * from rmProductBOMSales").NewRow();
                {
                    drHdr["CategoryID"] = strCategoryID;
                    drHdr["SetID"] = strSetID;
                    drHdr["ProductFItemID"] = strProductFItemID;
                   
                    drHdr["CategoryName"] = strCategoryName;
                    drHdr["SetName"] = strSetName;
                    drHdr["ProductName"] = strProductName;

                    drHdr["VersionNo"] = strNewVisionNo;
                    drHdr["TechnicsName"] = drOldHdr["TechnicsName"]; 
                    //drHdr["Creator"] = drOldHdr["Creator"].ToString();
                    //drHdr["CreateDate"] = drOldHdr["CreateDate"];
                    
                    foreach (DataColumn drCol in drOldHdr.Table.Columns)
                    {
                        if (drCol.ToString().Trim().ToUpper() == "RECID" || drCol.ToString().Trim().ToUpper() == "RECSTAMP" || drCol.ToString().Trim ().ToUpper ()=="VERSIONNO")
                        {
                        }
                        else 
                        {
                            drHdr[drCol.ToString()] = drOldHdr[drCol.ToString()];
                        }
                    }
                    drHdr["Creator"] = SysVar.UserName;  //drOldHdr["Creator"].ToString();
                    drHdr["CreateDate"] = DateTime.Now.Date;  // drOldHdr["CreateDate"];
                    DataFunctions.UpdateTable(dbcon, Fields , "rmProductBOMSales", drHdr, FormStatus.AddNew);
                    DataFunctions.UpdateTable(dbcon, ParameterFields, "rmProductBOMSales", drHdr, FormStatus.Update);
                }

                string strSQL = "select A.*, '" + strProductName + "' as ProductName  from rmProductBOMDetailSales A  where A.CategoryID='{0}' and A.SetID='{1}' and A.ProductFItemID='{2}' and VersionNo='{3}'  order by A.RowSeqNo";
                strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, strOldVersion);
                DataTable dtOld = dbcon.GetDataTable(strSQL);
                foreach (DataRow dr in dtOld.Rows)
                {
                    DataRow drBOM = dbcon.GetDataSet("select top 0 * from rmProductBOMDetailSales").Tables[0].NewRow();

                    drBOM["SeqNo"] = dr["SeqNo"].ToString().TrimEnd();
                    drBOM["RowSeqNo"] = dr["RowSeqNo"].ToString().Trim();
                    drBOM["WKID"] = dr["WKID"].ToString().TrimEnd();
                    drBOM["WKName"] = DataFunctions.GetFieldValue(dbcon, "rmWorkingProcedure", "WorkingProcedureName", " WorkingProcedureID='" + dr["WorkingProcedureID"] + "'");
                    drBOM["VersionNo"] = strNewVisionNo;
                    drBOM["ProductFItemID"] = strProductFItemID;
                    drBOM["ProductName"] = DataFunctions.GetFieldValue(dbcon, "rmProduct", "ProductName", " ProductFItemID='" + dr["ProductFItemID"] + "'");
                    drBOM["TechnicsName"] = dr["TechnicsName"].ToString().TrimEnd();
                    drBOM["CategoryID"] = dr["CategoryID"].ToString().TrimEnd();
                    drBOM["CategoryName"] = DataFunctions.GetFieldValue(dbcon, "rmCategory", "CategoryName", " CategoryID='" + dr["CategoryID"] + "'");
                    drBOM["SetID"] = CvtFunc.ToString(dr["SetID"]);
                    drBOM["SetName"] = DataFunctions.GetFieldValue(dbcon, "rmSet", "SetName", " SetID='" + dr["SetID"] + "'");
                    drBOM["FItemID"] = CvtFunc.ToString(dr["FItemID"]);
                    drBOM["ItemNo"] = DataFunctions.GetFieldValue(dbcon, "imInvMaster", "ItemNo", " ProductFItemID='" + dr["ProductFItemID"] + "'");
                    drBOM["IsKeyWK"] = CvtFunc.ToBoolean(dr["IsKeyWK"]);
                    drBOM["ItemName"] = DataFunctions.GetFieldValue(dbcon, "imInvMaster", "ItemName", " ProductFItemID='" + dr["ProductFItemID"] + "'");
                    drBOM["SupNo"] = CvtFunc.ToString(dr["SupNo"]);
                    drBOM["SupName"] = CvtFunc.ToString(dr["SupName"]);
                    drBOM["LabourCost"] = BaseFunctions.FormatNumeric(dr["LabourCost"], "Ns.4");
                    drBOM["Price"] = CvtFunc.ToDouble  ( DataFunctions.GetFieldValue ( dbcon, "iminvmaster","MarketCost","itemno='"+ drBOM["ItemNo"].ToString()  +"' and supno='"+ drBOM ["SupNo"].ToString () +"'"))  ; //BaseFunctions.FormatNumeric(dr["Price"], "Ns.3");
                    drBOM["PriceUnit"] = CvtFunc.ToString(dr["PriceUnit"]);
                    drBOM["Qty"] = BaseFunctions.FormatNumeric(CvtFunc.ToDouble(dr["Qty"]).ToString("F8"), "Ns.8");
                    drBOM["QtyWastage"] = BaseFunctions.FormatNumeric(dr["QtyWastage"], "Ns.2");
                    drBOM["WastageRate"] = BaseFunctions.FormatNumeric(dr["WastageRate"], "Ns.2");
                    drBOM["TotalCost"] = BaseFunctions.FormatNumeric(dr["TotalCost"], "Ns.2");
                    drBOM["DetailCost"] = BaseFunctions.FormatNumeric(dr["DetailCost"], "Ns.4");
                    drBOM["IsInputWK"] = dr["IsInputWK"].ToString();
                    drBOM["IsKeyWK"] = dr["IsKeyWK"].ToString();
                    if (!UpdateBOMDetail(dbcon, drBOM))
                    {
                        return false;
                    }

                    // Init  rmWKProductFormula  
                    strSQL = "select * from rmWKProductFormulaSales where BOMRecID='" + CvtFunc.ToString(dr["RecID"]) + "'";
                    DataTable dtFormula = dbcon.GetDataTable(strSQL);
                    foreach (DataRow drFormula in dtFormula.Rows)
                    {
                        strSQL = @"Insert Into rmWKProductFormulaSales (BOMRecID,ProductName,WKFormula)
                              VALUES ('{0}','{1}','{2}')";
                        strSQL = string.Format(strSQL, drBOM["RecID"], drBOM["ProductName"], drFormula["WKFormula"]);
                        dbcon.ExecuteSql(strSQL);
                    }
                }

                strSQL = "select A.*  from rmProductBOMLabourCostSales A where A.CategoryID='{0}' and A.SetID='{1}' and A.ProductFItemID='{2}' and A.VersionNo='{3}' order by a.RowSeqNo ";
                strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, strOldVersion);
                dtOld = dbcon.GetDataTable(strSQL);
                foreach (DataRow dr in dtOld.Rows)
                {
                    DataRow drBOM = dbcon.GetDataSet("select  top 0 * from rmProductBOMLabourCostSales").Tables[0].NewRow();

                    drBOM["SeqNo"] = dr["SeqNo"].ToString().TrimEnd();
                    drBOM["RowSeqNo"] = dr["RowSeqNo"].ToString().Trim();
                    drBOM["WKID"] = dr["WKID"].ToString().TrimEnd();
                    drBOM["WKName"] = dr["WKName"].ToString().TrimEnd();
                    drBOM["ProductFItemID"] = dr["ProductFItemID"].ToString().TrimEnd();
                    drBOM["ProductName"] = DataFunctions.GetFieldValue(dbcon, "rmProduct", "ProductName", " ProductFItemID='" + dr["ProductFItemID"] + "'");
                    drBOM["TechnicsName"] = dr["TechnicsName"].ToString().TrimEnd();
                    drBOM["CategoryID"] = dr["CategoryID"].ToString().TrimEnd();
                    drBOM["CategoryName"] = DataFunctions.GetFieldValue(dbcon, "rmCategory", "CategoryName", " CategoryID='" + dr["CategoryID"] + "'");
                    drBOM["SetID"] = CvtFunc.ToString(dr["SetID"]);
                    drBOM["SetName"] = DataFunctions.GetFieldValue(dbcon, "rmSet", "SetName", " SetID='" + dr["SetID"] + "'");
                    drBOM["VersionNo"] = strNewVisionNo;
                    drBOM["CalUnit"] = CvtFunc.ToString(dr["CalUnit"]);
                    drBOM["CutType"] = CvtFunc.ToString(dr["CutType"]);
                    drBOM["TotalQty"] = BaseFunctions.FormatNumeric(dr["TotalQty"], "Ns.4");
                    drBOM["StandardQty"] = BaseFunctions.FormatNumeric(dr["StandardQty"], "Ns.3");
                    drBOM["SpendHours"] = CvtFunc.ToString(dr["SpendHours"]);
                    drBOM["StandardWage"] = BaseFunctions.FormatNumeric(dr["StandardWage"], "Ns.2");
                    drBOM["StandardWage26"] = BaseFunctions.FormatNumeric(dr["StandardWage26"], "Ns.2");
                    drBOM["UnitCost"] = BaseFunctions.FormatNumeric(dr["UnitCost"], "Ns.2");
                    drBOM["Remark"] = CvtFunc.ToString(dr["Remark"]);
                    
                    double dblWorkCost = DataFunctions.GetCategoryWKWorkCost(dbcon, CvtFunc.ToString(drBOM["CategoryID"]), CvtFunc.ToString(drBOM["WKID"]));
                    if (dblWorkCost > 0)
                    {
                        drBOM["WorkCost"] = BaseFunctions.FormatNumeric(dblWorkCost, "Ns.2");
                    }
                    else
                    {
                        drBOM["WorkCost"] = CvtFunc.ToString(dr["WorkCost"]);
                    }
                    drBOM["FItemID"] = CvtFunc.ToString(dr["FItemID"]);
                    drBOM["ItemNo"] = DataFunctions.GetFieldValue(dbcon,"imInvMaster","ItemNo","FItemID='"+ drBOM["FItemID"] +"'"); 
                    if (!UpdateLabourCostDetail(dbcon, drBOM))
                    {
                        return false;
                    }
                    // Init  rmWKProductFormula  
                    strSQL = "select * from rmWKFormulaOtherProductBOMSales where BOMRecID='" + CvtFunc.ToString(dr["RecID"]) + "'";
                    DataTable dtFormula = dbcon.GetDataTable(strSQL);
                    foreach (DataRow drFormula in dtFormula.Rows)
                    {
                        strSQL = @"Insert Into rmWKFormulaOtherProductBOMSales (BOMRecID,ProductName,WKFormula)
                              VALUES ('{0}','{1}','{2}')";
                        strSQL = string.Format(strSQL, drBOM["RecID"], drBOM["ProductName"], drFormula["WKFormula"]);
                        dbcon.ExecuteSql(strSQL);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }

        }
    }
}
